package readExcel.utils;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;

/**
 * Author: 灵枢
 * Date: 2018/12/05
 * Time: 17:21
 * Description:读取Excel数据
 */
//POI
public class ExcelData {
    private XSSFSheet sheet;

    public XSSFSheet getSheet() {
        return sheet;
    }

    public void setSheet(XSSFSheet sheet) {
        this.sheet = sheet;
    }

    /**
     * 构造函数，初始化excel数据
     * @param filePath  excel路径
     * @param sheetName sheet表名
     */
    ExcelData(String filePath,String sheetName){
        FileInputStream fileInputStream = null;
        try {
            fileInputStream = new FileInputStream(filePath);
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            //获取sheet
            sheet = workbook.getSheet(sheetName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据行和列的索引获取单元格的数据
     * @param row
     * @param column
     * @return
     */
    public String getExcelDateByIndex(int row,int column){
        XSSFRow row1 = sheet.getRow(row -1);
        String cell = row1.getCell(column -1).toString();
        return cell;
    }

    /**
     * 根据某一列值为“******”的这一行，来获取该行第x列的值
     * @param caseName
     * @return
     */
    public String getCellByCaseName(String caseName){
        String operateSteps = "查无此--->" + caseName;
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=0; i<rows; i++){
            XSSFRow row = sheet.getRow(i);
            int numOfColumn = row.getPhysicalNumberOfCells();
            for(int j = 0; j < numOfColumn; j++){
                String cell = row.getCell(j).toString();
                if(cell.equals(caseName)){
                    operateSteps = "在第"+ i +"行,第" + j +"列,有" + caseName;
                    break;
                }
            }
        }
        return operateSteps;
    }

    //打印excel数据
    public  void readExcelData(){
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=0;i<rows;i++){
            //获取列数
            XSSFRow row = sheet.getRow(i);
            int columns = row.getPhysicalNumberOfCells();
            for(int j=0;j<columns;j++){
                String cell = row.getCell(j).toString();
                System.out.println(cell);
            }
        }
    }

    //测试方法
    public static void main(String[] args){
        /**
         * 测试方法一
         */
        System.out.println("========== 测试方法 1 ========================");
        ExcelData sheet1 = new ExcelData("C:\\Users\\mfg\\Desktop/test.xlsx", "Sheet1");
        sheet1.readExcelData();

        //获取第二行第4列
        String cell2 = sheet1.getExcelDateByIndex(1, 3);
        //根据第3列值为“customer23”的这一行，来获取该行第2列的值
        String cell3 = sheet1.getCellByCaseName("张三");
        System.out.println( "cell2\t" + cell2);
        System.out.println("cell3\t" + cell3);

        System.out.println("\n====================== 测试方法 2 ===============================\n");
        System.out.println(System.getProperties());
        try {
            XSSFWorkbook workbook = new XSSFWorkbook("C:\\Users\\mfg\\Desktop\\test.xlsx");
            Sheet sheet = workbook.getSheet("Sheet1");
            int numOfRow = sheet.getPhysicalNumberOfRows();
            System.out.println(numOfRow);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

